﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using Common;
using ExtBusiness.Common;
using WF_Business;
using ChBusiness;
using ChBusiness.Common;

namespace ExtBusiness.Interface
{
    public class InterfaceOperation
    {
        /// <summary>
        /// 分析xml数据并形成数据集
        /// </summary>
        /// <param name="strXml">xml文件内容</param>
        /// <returns></returns>
        public DataSet GetDatasetFromXml(string strXml)
        {
            DataSet dsTemp = new DataSet();
            System.IO.StringReader reader = new System.IO.StringReader(strXml);
            dsTemp.ReadXml(reader);
            return dsTemp;
        }

        /// <summary>
        /// 获取所有未保存成功的XML内容
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllStoreXml()
        {
            string strSql = @"select * from XT_INTERFACE_RESULT where post_flag=100 ";

            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            return dtReturn;
        }

        /// <summary>
        /// 更新未交换成功数据的标志位
        /// </summary>
        /// <param name="strId">未交换成功数据的id</param>
        public void UpdatePostFlag(string strId)
        {
            string strSql = string.Format(@"update XT_INTERFACE_RESULT t set t.post_flag=1 where t.id='{0}'",strId);

            SysParams.OAConnection().RunSql(strSql);
        }

        /// <summary>
        /// 插入更新异常的数据
        /// </summary>
        /// <param name="strXml">xml数据</param>
        /// <param name="strExMsg">拓展消息</param>
        /// <param name="nFlag">异常信息的标志位</param>
        public void UpdateExceptionAndXml(string strXml,string strExMsg,int nFlag)
        {
            string strSql = @"insert into XT_INTERFACE_RESULT(POST_XML,ERROR_INFO,FLAG)
                values(:xmldata,:errorInfo,:flag)";

            System.Data.IDataParameter[] idp = new System.Data.OracleClient.OracleParameter[3];
            idp[0] = new OracleParameter(":xmldata", OracleType.Clob);
            idp[0].Value = strXml;
            idp[1] = new OracleParameter(":errorInfo", OracleType.VarChar);
            idp[1].Value = strExMsg;
            idp[2] = new OracleParameter(":flag", OracleType.Int32);
            idp[2].Value = nFlag;
            SysParams.OAConnection().RunSql(strSql, ref idp);
        }

       
        /// <summary>
        /// 获取用户信息的xml数据
        /// </summary>
        /// <param name="strId">用户的id值</param>
        /// <param name="strOperation">操作类型</param>
        /// <returns></returns>
        public string GetUserInfoXml(string strId,string strOperation)
        {
            string strReturn = string.Empty;

            string strSql = string.Format(@"select USERID ""userID"",USERNAME ""userName"", USERPWD ""userPwd"",TEL ""tel"",MOBILE ""mobile"",IDTYPE ""idType"",IDNUMBER ""idNumber"",FAX ""fax"",EMAIL ""email"",POSTCODE ""postcode""  from sys_user t  where t.id='{0}' and SYNC_TYPE=2", strId);

            DataTable dtReturn;
            SysParams.OAConnection().RunSql(strSql, out dtReturn);

            if (dtReturn.Rows.Count > 0)
            {
                strReturn = "<?xml version='1.0' encoding='utf-8' ?><DataSet>";
                foreach (DataRow drTemp in dtReturn.Rows)
                {
                    strReturn += string.Format("<Record operation='{0}'>",strOperation);
                    foreach (DataColumn dcOne in dtReturn.Columns)
                    {
                        strReturn += string.Format("<{1}>{0}</{1}>", drTemp[dcOne.ColumnName], dcOne.ColumnName);
                    }
                    strReturn += "</Record>";
                }
                strReturn += "</DataSet>";
            }

            return strReturn;
        }

        /// <summary>
        /// 获取数据信息
        /// </summary>
        /// <param name="drTemp">数据行</param>
        /// <param name="strColName">列明</param>
        /// <param name="nDataType">数据类型</param>
        /// <returns></returns>
        public string GetDataInfo(DataRow drTemp, string strColName,int nDataType)
        {
            string strReturn = string.Empty;
            if (drTemp.Table.Columns.Contains(strColName))
            {
                strReturn = drTemp[strColName].ToString();
            }
            switch (nDataType)
            {
                case 1://日期
                    strReturn = string.Format("to_date('{0}','yyyy-mm-dd')", strReturn);
                    break;
                case 2://时间
                    strReturn = string.Format("to_date('{0}','hh24:mi:ss')", strReturn);
                    break;

            }

            return strReturn;
        }

        /// <summary>
        /// 将通过GetApplication接口获取的数据拷贝到数据库当中
        /// </summary>
        /// <param name="ds">数据集合</param>
        /// <!--addby zhongjian 20090907-->
        public void InsertInfaceData(DataSet ds)
        {
            string strSql = string.Empty;
            try
            {
                for (int i = 0; i<ds.Tables.Count; ++i)
                {
                    DataRow drTemp = ds.Tables[0].Rows[i];
                    string strappID = GetDataInfo( drTemp,"appID",0);//申请单号
                    string strappDate = GetDataInfo(drTemp, "appDate", 1);//申请日期
                    string strappTime = GetDataInfo(drTemp, "appTime", 2); //申请时间
                    string strappUserCode = GetDataInfo(drTemp, "appUserCode", 0);//用户代码
                    string strappOrgName = GetDataInfo(drTemp, "appOrgName", 0);//单位名称
                    string strappAddress = GetDataInfo(drTemp, "appAddress", 0);//详细地址1
                    string strappOrgID = GetDataInfo(drTemp, "appOrgID", 0); //申请人代码
                    string strappPerson = GetDataInfo(drTemp, "appPerson", 0); //经办人姓名
                    string strappPersonID = GetDataInfo(drTemp, "appPersonID", 0);//身份证号码
                    string strappPostcode = GetDataInfo(drTemp, "appPostcode", 0); //邮政编码1
                    string strappTel = GetDataInfo(drTemp, "appTel", 0);//联系电话1   --11

                    string straecOrgName = GetDataInfo(drTemp, "secOrgName", 0); //机构名称
                    string strsecPostcode = GetDataInfo(drTemp, "secPostcode", 0); //邮政编码2
                    string strsecPerson = GetDataInfo(drTemp, "secPerson", 0); //人员姓名
                    string strsecTel = GetDataInfo(drTemp, "secTel", 0); //联系电话2
                    string strsecAddress = GetDataInfo(drTemp, "secAddress", 0); //详细地址2
                    string strsecEmail = GetDataInfo(drTemp, "secEmail", 0); //电子邮箱2   --17

                    string strprovOrgName = GetDataInfo(drTemp, "provOrgName", 0);//机关名称
                    string strprovPerson = GetDataInfo(drTemp, "provPerson", 0);//承办人姓名                    
                    string strprovPostcode = GetDataInfo(drTemp, "provPostcode", 0);//邮政编码3
                    string strprovTel = GetDataInfo(drTemp, "provTel", 0);//联系电话3
                    string strprovAddress = GetDataInfo(drTemp, "provAddress", 0);//详细地址3
                    string strpurpose = GetDataInfo(drTemp, "purpose", 0);//使用目的
                    string strprojectName = GetDataInfo(drTemp, "projectName", 0);//项目来源
                    string strproductName = GetDataInfo(drTemp, "productName", 0);//成果资料名称
                    string strproductParts = GetDataInfo(drTemp, "productParts", 0);//种类    --26

                    string straqrcnQz = GetDataInfo(drTemp, "aqrcnQz", 0);//签字1
                    string straqcnQzrq = GetDataInfo(drTemp, "aqcnQzrq", 1);//日期1
                    string strfzbmjgqz = GetDataInfo(drTemp, "fzbmjgqz", 0);//签字2
                    string strfzbmjgQzrq = GetDataInfo(drTemp, "fzbmjgQzrq", 1);//日期2
                    string strcjzmhQz = GetDataInfo(drTemp, "cjzmhQz", 0);//签字3
                    string strcjzmhQzrq = GetDataInfo(drTemp, "cjzmhQzrq", 1);//日期3
                    string strnote = GetDataInfo(drTemp, "note", 0);//备注   --33

                    string strUserID = GetDataInfo(drTemp, "userid", 0);//编号   --34

                    strSql = string.Format(@"insert into UT_测绘成果使用申请表(appid,申请日期,申请时间,用户代码,单位名称,详细地址1,
                            申请人代码,经办人姓名,身份证号码,邮政编码1,联系电话1,机构名称,邮政编码2,人员姓名,联系电话2,详细地址2,电子邮箱2,机关名称,
                            承办人姓名,邮政编码3,联系电话3,详细地址3,使用目的,项目来源,成果资料名称,种类,签字1,日期1,签字2,日期2,签字3,日期3,备注,编号,userid)
                            values ('{0}',{1},{2},'{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',
                            '{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}',
                            {27},'{28}',{29},'{30}',{31},'{32}','{0}','{33}')",
                            strappID, strappDate, strappTime, strappUserCode, strappOrgName, strappAddress, strappOrgID, strappPerson, strappPersonID,
                            strappPostcode, strappTel, straecOrgName, strsecPostcode, strsecPerson, strsecTel, strsecAddress, strsecEmail,strprovOrgName,
                            strprovPerson, strprovPostcode, strprovTel, strprovAddress, strpurpose, strprojectName, strproductName, strproductParts,
                            straqrcnQz, straqcnQzrq, strfzbmjgqz, strfzbmjgQzrq, strcjzmhQz, strcjzmhQzrq, strnote, strUserID);

                    SysParams.OAConnection().RunSql(strSql);

                    //添加提交受理表 addby zhongjian 20091211
                    strSql = string.Format(@"insert into xt_submit_info(iid,flowname,requester,submitflag,flowtype,SYNC_TYPE) 
                            values ('{0}','{1}','{2}','{3}','{4}','{5}')",
                            strappID, SystemConfig.InterfaceSerialName, strUserID, "1", "国家涉密基础测绘成果资料提供使用审批", "2");
                    SysParams.OAConnection().RunSql(strSql);
                }

            }
            catch (Exception ex)
            {
                string strSql1 = "";
                strSql1 = "insert into XT_INTERFACE_RESULT (error_info) values ('" + ex.ToString() + "提示:" + strSql + "')";
                SysParams.OAConnection().RunSql(strSql1);
            }
        }
        /// <summary>
        /// 获取待受理的 秘密基础测绘成果使用申请表 相关信息
        /// </summary>
        /// <param name="strappOrgName">单位名称</param>
        /// <param name="strappPerson">经办人姓名</param>
        /// <param name="strappPersonID">身份证号码</param>
        /// <param name="straecOrgName">机构名称</param>
        /// <param name="strsecPerson">人员姓名</param>
        /// <param name="strprovOrgName">机关名称</param>
        /// <param name="strprovPerson">承办人姓名</param>
        /// <param name="strstartappDate">申请日期(从)</param>
        /// <param name="strendappDate">申请日期(到)</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20090908-->
        public DataTable GetUtInfo(string strappOrgName, string strappPerson, string strappPersonID, string straecOrgName, string strsecPerson,
                                    string strprovOrgName, string strprovPerson, string strstartappDate,string strendappDate)
        {
            string strSql = "";
            DataTable dtTemp;
            strSql = "select rownum 序号,iid,appid,单位名称,经办人姓名,身份证号码,机构名称,人员姓名,机关名称,承办人姓名,申请日期 from UT_测绘成果使用申请表 where iid is null and appid>0";
            if (!string.IsNullOrEmpty(strappOrgName))
            {
                strSql = strSql + " and 单位名称 like '%" + strappOrgName + "%'";
            }
            if (!string.IsNullOrEmpty(strappPerson))
            {
                strSql = strSql + " and 经办人姓名 like '%" + strappPerson + "%'";
            }
            if (!string.IsNullOrEmpty(strappPersonID))
            {
                strSql = strSql + " and 身份证号码 like '%" + strappPersonID + "%'";
            }
            if (!string.IsNullOrEmpty(straecOrgName))
            {
                strSql = strSql + " and 机构名称 like '%" + straecOrgName + "%'";
            }
            if (!string.IsNullOrEmpty(strsecPerson))
            {
                strSql = strSql + " and 人员姓名 like '%" + strsecPerson + "%'";
            }
            if (!string.IsNullOrEmpty(strprovOrgName))
            {
                strSql = strSql + " and 机关名称 like '%" + strprovOrgName + "%'";
            }
            if (!string.IsNullOrEmpty(strprovPerson))
            {
                strSql = strSql + " and 承办人姓名 like '%" + strprovPerson + "%'";
            }
            if (!string.IsNullOrEmpty(strstartappDate))
            {
                strSql = strSql + " and 申请日期 >= to_date('" + strstartappDate + "','yyyy-mm-dd')";
            }
            if (!string.IsNullOrEmpty(strendappDate))
            {
                strSql = strSql + " and 申请日期 <= to_date('" + strendappDate + "','yyyy-mm-dd')";
            }
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 按流程获取待处理信息
        /// </summary>
        /// <param name="strFlowName">流程名称</param>
        /// <param name="strappOrgName">单位名称</param>
        /// <param name="straecOrgName">机构名称</param>
        /// <returns></returns>
        /// <!--addby zhongjian 20091019-->
        public DataTable GetUtInfoOld(string strFlowName, string strappOrgName, string straecOrgName)
        {
            string strSql = "";
            DataTable dtTemp;
            strSql = @"select b.iid,
                   b.appid,
                   b.单位名称,
                   b.经办人姓名,
                   b.身份证号码,
                   b.机构名称,
                   b.人员姓名,
                   b.机关名称,
                   b.承办人姓名,
                   b.申请日期,
                   C.WID,
                   A.FLOWNAME
              from xt_submit_info a, UT_测绘成果使用申请表 b, st_workflow c
             where a.iid = b.iid
               and b.appid is null
               and a.submitflag = 1
               AND C.WNAME = (A.FLOWNAME)
               AND C.ROT = 0";
            if (!string.IsNullOrEmpty(strFlowName))
            {
                strSql += string.Format(" and a.flowname='{0}'", strFlowName);
            }
            if (!string.IsNullOrEmpty(strappOrgName))
            {
                strSql += string.Format(" and b.单位名称='{0}'", strappOrgName);
            }
            if (!string.IsNullOrEmpty(straecOrgName))
            {
                strSql += string.Format(" and b.机构名称='{0}'", straecOrgName);
            }

            strSql += " order by b.iid desc";
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }

        /// <summary>
        /// 将通过GetApplication接口获取的数据拷贝到数据库当中
        /// </summary>
        /// <param name="ds">数据集合</param>
        /// <param name="strUserName">获取数据人员名称</param>
        /// <!--addby zhongjian 20090907-->
        public void InsertInfaceData(DataSet ds, string strUserName)
        {
            string strSql = string.Empty;
            try
            {
                for (int i = 0; i < ds.Tables.Count; ++i)
                {
                    DataRow drTemp = ds.Tables[0].Rows[i];
                    string strappID = GetDataInfo(drTemp, "appID", 0);//申请单号
                    string strappDate = GetDataInfo(drTemp, "appDate", 1);//申请日期
                    string strappTime = GetDataInfo(drTemp, "appTime", 2); //申请时间
                    string strappUserCode = GetDataInfo(drTemp, "appUserCode", 0);//用户代码
                    string strappOrgName = GetDataInfo(drTemp, "appOrgName", 0);//单位名称
                    string strappAddress = GetDataInfo(drTemp, "appAddress", 0);//详细地址1
                    string strappOrgID = GetDataInfo(drTemp, "appOrgID", 0); //申请人代码
                    string strappPerson = GetDataInfo(drTemp, "appPerson", 0); //经办人姓名
                    string strappPersonID = GetDataInfo(drTemp, "appPersonID", 0);//身份证号码
                    string strappPostcode = GetDataInfo(drTemp, "appPostcode", 0); //邮政编码1
                    string strappTel = GetDataInfo(drTemp, "appTel", 0);//联系电话1   --11

                    string straecOrgName = GetDataInfo(drTemp, "secOrgName", 0); //机构名称
                    string strsecPostcode = GetDataInfo(drTemp, "secPostcode", 0); //邮政编码2
                    string strsecPerson = GetDataInfo(drTemp, "secPerson", 0); //人员姓名
                    string strsecTel = GetDataInfo(drTemp, "secTel", 0); //联系电话2
                    string strsecAddress = GetDataInfo(drTemp, "secAddress", 0); //详细地址2
                    string strsecEmail = GetDataInfo(drTemp, "secEmail", 0); //电子邮箱2   --17

                    string strprovOrgName = GetDataInfo(drTemp, "provOrgName", 0);//机关名称
                    string strprovPerson = GetDataInfo(drTemp, "provPerson", 0);//承办人姓名                    
                    string strprovPostcode = GetDataInfo(drTemp, "provPostcode", 0);//邮政编码3
                    string strprovTel = GetDataInfo(drTemp, "provTel", 0);//联系电话3
                    string strprovAddress = GetDataInfo(drTemp, "provAddress", 0);//详细地址3
                    string strpurpose = GetDataInfo(drTemp, "purpose", 0);//使用目的
                    //string strprojectName = GetDataInfo(drTemp, "projectName", 0);//项目来源 //项目来源已细化 20100309
                    string strproductName = GetDataInfo(drTemp, "productName", 0);//成果资料名称
                    string strproductParts = GetDataInfo(drTemp, "productParts", 0);//种类    --26

                    string straqrcnQz = GetDataInfo(drTemp, "aqrcnQz", 0);//签字1
                    string straqcnQzrq = GetDataInfo(drTemp, "aqcnQzrq", 1);//日期1
                    string strfzbmjgqz = GetDataInfo(drTemp, "fzbmjgqz", 0);//签字2
                    string strfzbmjgQzrq = GetDataInfo(drTemp, "fzbmjgQzrq", 1);//日期2
                    string strcjzmhQz = GetDataInfo(drTemp, "cjzmhQz", 0);//签字3
                    string strcjzmhQzrq = GetDataInfo(drTemp, "cjzmhQzrq", 1);//日期3
                    string strnote = GetDataInfo(drTemp, "note", 0);//备注   --33

                    string strUserID = GetDataInfo(drTemp, "userid", 0);//编号   --34

                    //==begin 由于表单字段的修改,因此添加以下字段(项目来源由以下内容组成)addby zhongjian 20100309
                    string strprojectName = GetDataInfo(drTemp, "projectName", 0);// 项目名称 
                    string strprojectOrg = GetDataInfo(drTemp, "projectOrg", 0);// 批准单位名称
                    string strprojectFileNum = GetDataInfo(drTemp, "projectFileNum", 0);// 批准文号
                    string strprojectOrgType = GetDataInfo(drTemp, "projectOrgType", 0);// 批准单位性质(国家机关、事业、企业、军队、其他)
                    string strprojectOrgType1 = "0"; //单位性质1==国家机关
                    string strprojectOrgType2 = "0"; //单位性质2==事业
                    string strprojectOrgType3 = "0"; //单位性质3==企业
                    string strprojectOrgType4 = "0"; //单位性质4==军队
                    string strprojectOrgType5 = "0"; //单位性质5==其他     --42
                    if (strprojectOrgType == "机关")
                        strprojectOrgType1 = "1";
                    else if (strprojectOrgType == "事业")
                        strprojectOrgType2 = "1";
                    else if (strprojectOrgType == "企业")
                        strprojectOrgType3 = "1";
                    else if (strprojectOrgType == "军队")
                        strprojectOrgType4 = "1";
                    else if (strprojectOrgType == "其他")
                        strprojectOrgType5 = "1";
                    //==end 

                    string strappEmail = GetDataInfo(drTemp, "appEmail", 0);// 申请人邮箱 

                    //添加IID的值:在这里添加为APPID的值.不让其为空,目的是在受理之前可以查询表单信息(第35的值) update by zhongjian 20100303
                    strSql = string.Format(@"insert into UT_测绘成果使用申请表(appid,申请日期,申请时间,用户代码,单位名称,详细地址1,
                            申请人代码,经办人姓名,身份证号码,邮政编码1,联系电话1,机构名称,邮政编码2,人员姓名,联系电话2,详细地址2,电子邮箱2,机关名称,
                            承办人姓名,邮政编码3,联系电话3,详细地址3,使用目的,项目来源,成果资料名称,种类,签字1,日期1,签字2,日期2,签字3,日期3,备注,编号,
                            userid,iid,项目名称,批准单位名称,批准文号,单位性质1,单位性质2,单位性质3,单位性质4,单位性质5,电子邮箱1)
                            values ('{0}',{1},{2},'{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',
                            '{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}',
                            {27},'{28}',{29},'{30}',{31},'{32}','{0}','{33}','{34}','{35}','{36}','{37}','{38}','{39}','{40}','{41}','{42}','{43}')",
                            strappID, strappDate, strappTime, strappUserCode, strappOrgName, strappAddress, strappOrgID, strappPerson, strappPersonID,
                            strappPostcode, strappTel, straecOrgName, strsecPostcode, strsecPerson, strsecTel, strsecAddress, strsecEmail, strprovOrgName,
                            strprovPerson, strprovPostcode, strprovTel, strprovAddress, strpurpose, strprojectName, strproductName, strproductParts,
                            straqrcnQz, straqcnQzrq, strfzbmjgqz, strfzbmjgQzrq, strcjzmhQz, strcjzmhQzrq, strnote, strUserID, strappID,
                            strprojectName, strprojectOrg, strprojectFileNum, strprojectOrgType1, strprojectOrgType2, strprojectOrgType3,
                            strprojectOrgType4, strprojectOrgType5, strappEmail);

                    SysParams.OAConnection().RunSql(strSql);

                    //添加提交受理表 addby zhongjian 20091211
                    strSql = string.Format(@"insert into xt_submit_info(iid,flowname,requester,submitflag,flowtype,SYNC_TYPE) 
                            values ('{0}','{1}','{2}','{3}','{4}','{5}')",
                            strappID, SystemConfig.InterfaceSerialName, strUserID, "1", "国家涉密基础测绘成果资料提供使用审批", "2");
                    SysParams.OAConnection().RunSql(strSql);

                    //发送消息 add by zhongjian 2010421
                    ClsSubmitInfo clsSubmit = new ClsSubmitInfo();
                    clsSubmit.SetMsg(strappID, "已经提交");
                    //添加操作日志 add by zhongjian 2010421
                    //获取操作者ip地址
                    string myIP = IPOperation.GetIpAddress();

                    string strRemark = string.Format("从地信接口获取业务,并已发送提交的通知。申请人：{0}，申请编号为： {1}", strUserID, strappID);
                    SystemLogs.AddSystemLogs(strUserName, "add", strRemark, strSql, myIP);
                }

            }
            catch (Exception ex)
            {
                string strSql1 = "";
                strSql1 = "insert into XT_INTERFACE_RESULT (error_info) values ('" + ex.ToString() + "提示:" + strSql + "')";
                SysParams.OAConnection().RunSql(strSql1);
            }
        }

       
    }
}
